﻿using Md.Api.Entity.ModelDB;
using PetaPoco.NetCore;

namespace Md.Petapoco.Repositories.MdTrainingEducation;
/// <summary>
/// 系统字典数据仓储
/// </summary>
public class SysDictDataRep : BaseRep<SysDictDataModel>
{
    /// <summary>
    /// 通过字典编码获取系统字典信息
    /// </summary>
    /// <param name="dictCode">字典编码</param>
    /// <returns>SysDictDataModel</returns>
    public SysDictDataModel GetSysDictDataByDictCode(long dictCode)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select `dict_code`
                  ,`dict_sort`
                  ,`dict_label`
                  ,`dict_value`
                  ,`dict_type`
                  ,`css_class`
                  ,`list_class`
                  ,`is_default`
                  ,`status`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_dict_data`
            where 1 = 1 and `dict_code` = @0
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<SysDictDataModel>(sqlStr, dictCode);
    }
    /// <summary>
    /// 通过字典类型获取系统字典列表数据
    /// </summary>
    /// <param name="dictType">字典类型</param>
    /// <returns>IEnumerable<SysDictDataModel></returns>
    public IEnumerable<SysDictDataModel> GetSysDictDataListByDictType(string dictType)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select `dict_code`
                  ,`dict_sort`
                  ,`dict_label`
                  ,`dict_value`
                  ,`dict_type`
                  ,`css_class`
                  ,`list_class`
                  ,`is_default`
                  ,`status`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_dict_data`
            where 1 = 1 and `dict_type` = @0 order by `dict_sort` asc
         ");
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<SysDictDataModel>(sqlStr, dictType);
    }
    /// <summary>
    /// 获取系统字典列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>IEnumerable<SysDictDataModel></returns>
    public IEnumerable<SysDictDataModel> GetSysDictDataList(SysDictDataModel model, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.DictCode > 0)
        {
            sqlWhere += " and dict_code = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DictLabel))
        {
            sqlWhere += " and dict_label like @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DictValue))
        {
            sqlWhere += " and dict_value like  @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DictType))
        {
            sqlWhere += " and dict_type =  @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.IsDefault))
        {
            sqlWhere += " and is_default =  @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @7 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @8 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @9 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select `dict_code`
                  ,`dict_sort`
                  ,`dict_label`
                  ,`dict_value`
                  ,`dict_type`
                  ,`css_class`
                  ,`list_class`
                  ,`is_default`
                  ,`status`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_dict_data`
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<SysDictDataModel>(
            sqlStr,
            model.DictCode,
            string.Format(@"%{0}%", model.DictLabel),
            string.Format(@"%{0}%", model.DictValue),
            model.DictType,
            model.IsDefault,
            model.Status,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 分页获取系统字典列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="pageIndex">当前页</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>Page<SysDictDataModel></returns>
    public Page<SysDictDataModel> GetSysDictDataPageList(SysDictDataModel model, int pageIndex, int pageSize, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.DictCode > 0)
        {
            sqlWhere += " and dict_code = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DictLabel))
        {
            sqlWhere += " and dict_label like @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DictValue))
        {
            sqlWhere += " and dict_value like  @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DictType))
        {
            sqlWhere += " and dict_type =  @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.IsDefault))
        {
            sqlWhere += " and is_default =  @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @7 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @8 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @9 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select `dict_code`
                  ,`dict_sort`
                  ,`dict_label`
                  ,`dict_value`
                  ,`dict_type`
                  ,`css_class`
                  ,`list_class`
                  ,`is_default`
                  ,`status`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_dict_data`
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Page<SysDictDataModel>(
            pageIndex,
            pageSize,
            sqlStr,
            model.DictCode,
            string.Format(@"%{0}%", model.DictLabel),
            string.Format(@"%{0}%", model.DictValue),
            model.DictType,
            model.IsDefault,
            model.Status,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 通过字典编码更新系统字典
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>int</returns>
    public long UpdateSysDictDataByDictCode(SysDictDataModel model)
    {
        var sql = new Sql();

        sql.Append(" update `sys_dict_data` set ");

        #region ===set===
        if (!string.IsNullOrWhiteSpace(model.DictLabel))
        {
            sql.Append(" dict_label = @0, ", model.DictLabel);
        }

        if (!string.IsNullOrWhiteSpace(model.DictValue))
        {
            sql.Append(" dict_value = @0, ", model.DictValue);
        }

        if (model.DictSort > -1)
        {
            sql.Append(" dict_sort = @0, ", model.DictSort);
        }

        if (!string.IsNullOrWhiteSpace(model.DictType))
        {
            sql.Append(" dict_type = @0, ", model.DictType);
        }

        if(!string.IsNullOrWhiteSpace(model.CssClass))
        {
            sql.Append(" css_class = @0, ", model.CssClass);
        }

        if (!string.IsNullOrWhiteSpace(model.ListClass))
        {
            sql.Append(" list_class = @0, ", model.ListClass);
        }

        if (!string.IsNullOrWhiteSpace(model.IsDefault))
        {
            sql.Append(" is_default = @0, ", model.IsDefault);
        }

        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sql.Append(" status = @0, ", model.Status);
        }

        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sql.Append(" update_by = @0, ", model.UpdateBy);
        }

        if (!string.IsNullOrWhiteSpace(model.Remark))
        {
            sql.Append(" remark = @0, ", model.Remark);
        }

        sql.Append(" update_time = sysdate() ");
        #endregion

        sql.Append(" where 1 = 1 and dict_code = @0 ", model.DictCode);

        return MdTrainingEducationDB.GetInstance().Execute(sql);
    }
}

